package data

import (
	"commerce/common"
	"commerce/model"
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"
)

func ListSku(goodsId, pageNo, pageSize int, name string) (*common.Page, error) {

	var err error
	var whereSql = ""
	if goodsId > 0 {
		whereSql = fmt.Sprintf(" where goods_id = %d", goodsId)
	} else if len(name) > 0 {
		whereSql = composeSkuSearchQuerySql(name)
	}
	row := common.DB.QueryRow("select count(*) FROM goods_sku k" + whereSql)
	var totalRecords int
	if err = row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	pSql := "SELECT k.id, k.`name`, k.price, k.real_price, k.priority, k.`status`, k.main_img, g.name as goods_name, k.created_time, k.is_hot from goods_sku k INNER join goods g on k.goods_id = g.id" + whereSql
	if goodsId <= 0 {
		pSql += " LIMIT ?, ?"
	}
	stmt, err := common.DB.Prepare(pSql)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var rows *sql.Rows
	if goodsId <= 0 {
		rows, err = stmt.Query((pageNo-1)*pageSize, pageSize)
	} else {
		rows, err = stmt.Query()
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.Name, &k.Price, &k.RealPrice, &k.Priority,
			&k.Status, &k.MainImg, &k.GoodsName, &k.CreatedTimeStr, &k.IsHot); err != nil {
			return nil, fmt.Errorf("商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return common.NewPage(ks, pageNo, pageSize, totalRecords), nil
}

func ListSkuByGoodsId(goodsId, status int) ([]int, error) {

	sqlTpl := "select id FROM goods_sku where goods_id = ? and `status` = ?"
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(goodsId, status)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []int
	for rows.Next() {
		var id int
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("sku数据有误:%s", err.Error())
		}
		ks = append(ks, id)
	}
	return ks, nil
}

func GetSkuById(id int) (*model.Sku, error) {

	sqlTpl := "SELECT k.id, k.`name`, k.description, k.price, k.real_price, k.priority, k.`status`, k.main_img, k.goods_id, g.name as goods_name, g.brand_id, g.category_id FROM goods_sku k inner join goods g on k.goods_id = g.id where k.id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var k model.Sku
	if err = stmt.QueryRow(id).Scan(&k.Id, &k.Name, &k.Description, &k.Price, &k.RealPrice, &k.Priority,
		&k.Status, &k.MainImg, &k.GoodsId, &k.GoodsName, &k.BrandId, &k.CategoryId); err != nil {
		return nil, err
	}
	return &k, nil
}

func ListSkuAttrValueBySkuId(skuId int, flag bool) ([]model.SkuAttrValue, error) {

	sqlTpl := "select attr_id, `attr_name`, attr_value FROM sku_attr_value where sku_id = ?"
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(skuId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.SkuAttrValue
	for rows.Next() {
		k := model.SkuAttrValue{}
		if err := rows.Scan(&k.AttrId, &k.AttrName, &k.AttrValue); err != nil {
			return nil, fmt.Errorf("sku属性值数据有误:%s", err.Error())
		}
		//k.AttrName = k.AttrName + "_" + k.AttrValue
		if flag {
			k.AttrValue = fmt.Sprintf("%d_%s", k.AttrId, k.AttrValue)
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func ListGoodsSpec(goodsId int) ([]model.SkuAttrValue, error) {

	sqlTpl := "select s.attr_id, s.`attr_name`, s.attr_value, group_concat(distinct k.id) FROM goods_sku k " +
		"inner join sku_attr_value s on k.id = s.sku_id where k.goods_id = ? GROUP BY s.attr_id, s.attr_name, s.attr_value"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(goodsId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.SkuAttrValue
	for rows.Next() {
		k := model.SkuAttrValue{}
		if err := rows.Scan(&k.AttrId, &k.AttrName, &k.AttrValue, &k.SkuIds); err != nil {
			return nil, fmt.Errorf("根据goodsId 查询 sku属性值数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func ListSkuAttrValueByGoodsId(goodsId int) ([]model.SkuAttrValueVo, error) {

	sqlTpl := "select g.id, g.name, g.options FROM goods_attr g inner join goods_attr_value v on g.id = v.attr_id where v.goods_id = ?"
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(goodsId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.SkuAttrValueVo
	for rows.Next() {
		k := model.SkuAttrValueVo{}
		var kvs []model.SkuOptionKV
		if err := rows.Scan(&k.AttrId, &k.AttrName, &k.Options); err != nil {
			return nil, fmt.Errorf("sku属性值数据有误:%s", err.Error())
		}
		for _, v := range strings.Split(k.Options, ",") {
			kvs = append(kvs, model.SkuOptionKV{OptionVal: fmt.Sprintf("%d_%s", k.AttrId, v), OptionName: k.AttrName + "_" + v})
		}
		k.Options = ""
		k.AttrOptionList = kvs

		ks = append(ks, k)
	}
	return ks, nil
}

func AddSku(ctx context.Context, k model.Sku, attrInputs []string, attrValue []model.GoodsAttr) (int, error) {

	fail := func(err error) (int, error) {
		return 0, fmt.Errorf("AddSku: %v", err)
	}
	var err error
	tx, err := common.DB.BeginTx(ctx, nil)
	if err != nil {
		return fail(err)
	}
	/**
		Defer the transaction’s rollback. If the transaction succeeds,
	it will be committed before the function exits, making the deferred rollback call a no-op.
		If the transaction fails it won’t be committed,
	meaning that the rollback will be called as the function exits.
	*/
	defer tx.Rollback()

	sqlTpl := `insert into goods_sku(id, name, description, price, real_price, priority, status, main_img, goods_id, created_time, updated_time)
 		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	result, err := tx.Exec(sqlTpl, k.Id, k.Name, k.Description, k.Price, k.RealPrice, k.Priority, 0, k.MainImg, k.GoodsId,
		time.Now().Add(8*time.Hour), time.Now().Add(8*time.Hour))

	if err != nil {
		return fail(err)
	}
	id, err := result.LastInsertId()

	if err != nil {
		return fail(err)
	}
	// 插入商品sku属性值
	skuAttrValue := composeSkuAttrValueDBParam(attrInputs, int(id), attrValue)
	err = batchAddSkuPropertyVal(ctx, tx, skuAttrValue)
	if err != nil {
		return fail(err)
	}
	// 提交事务
	if err = tx.Commit(); err != nil {
		return fail(err)
	}
	return int(id), nil
}

func composeSkuAttrValueDBParam(attrInputs []string, id int, attrValue []model.GoodsAttr) []model.SkuAttrValue {
	var kv []string
	var skuAttrValue []model.SkuAttrValue
	var kav model.SkuAttrValue
	var attrId int

	var idNameMap = make(map[int]string, len(attrValue))
	for _, x := range attrValue {
		idNameMap[x.Id] = x.Name
	}

	for _, v := range attrInputs {
		kv = strings.Split(v, "_")
		attrId, _ = strconv.Atoi(kv[0])
		kav = model.SkuAttrValue{SkuId: id, AttrId: attrId, AttrName: idNameMap[attrId], AttrValue: kv[1]}
		skuAttrValue = append(skuAttrValue, kav)
	}
	return skuAttrValue
}

func batchAddSkuPropertyVal(ctx context.Context, tx *sql.Tx, value []model.SkuAttrValue) error {

	sqlTpl := "insert into sku_attr_value(sku_id, attr_id, attr_name, attr_value) VALUES (?, ?, ?, ?)"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	for _, v := range value {
		_, err = stmt.ExecContext(ctx, v.SkuId, v.AttrId, v.AttrName, v.AttrValue)
		if err != nil {
			return err
		}
	}
	return nil
}

func UpdateSku(ctx context.Context, k model.Sku, attrInputs []string, attrValue []model.GoodsAttr) (int, error) {

	fail := func(err error) (int, error) {
		return 0, fmt.Errorf("update sku: %v", err)
	}
	var err error
	tx, err := common.DB.BeginTx(ctx, nil)
	if err != nil {
		return fail(err)
	}
	defer tx.Rollback()

	sqlTpl := "update goods_sku set `name`=?, description = ?, price = ?, real_price = ?, priority = ?, main_img=?, updated_time=? WHERE id = ?"
	if len(k.MainImg) == 0 {
		sqlTpl = "update goods_sku set `name`=?, description = ?, price = ?, real_price = ?, priority = ?, updated_time=? WHERE id = ?"
	}
	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return fail(err)
	}
	defer stmt.Close()

	var result sql.Result
	if len(k.MainImg) > 0 {
		result, err = stmt.Exec(k.Name, k.Description, k.Price, k.RealPrice, k.Priority, k.MainImg, time.Now().Add(8*time.Hour), k.Id)
	} else {
		result, err = stmt.Exec(k.Name, k.Description, k.Price, k.RealPrice, k.Priority, time.Now().Add(8*time.Hour), k.Id)
	}
	if err != nil {
		return fail(fmt.Errorf("update sku(id:%d) err:%v", k.Id, err.Error()))
	}
	rowsAffected, err := result.RowsAffected()

	// 更新属性值（先删除再插入）
	err = deleteSkuAttrValueBySkuId(ctx, tx, k.Id)
	if err != nil {
		return fail(err)
	}
	skuAttrValue := composeSkuAttrValueDBParam(attrInputs, k.Id, attrValue)
	err = batchAddSkuPropertyVal(ctx, tx, skuAttrValue)
	if err != nil {
		return fail(err)
	}
	// 提交事务
	if err = tx.Commit(); err != nil {
		return fail(err)
	}

	return int(rowsAffected), err
}

func deleteSkuAttrValueBySkuId(ctx context.Context, tx *sql.Tx, skuId int) error {

	sqlTpl := "delete from sku_attr_value WHERE sku_id = ?"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.ExecContext(ctx, skuId)
	if err != nil {
		return fmt.Errorf("delete sku attr value by skuId:%d, err:%v", skuId, err.Error())
	}
	return err
}

func UpdateSkuStatus(id, status int) error {

	sqlTpl := "update goods_sku set status = ?, updated_time = ? WHERE id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(status, time.Now().Add(8*time.Hour), id)
	if err != nil {
		return fmt.Errorf("update sku status, id:%d, err:%v", id, err.Error())
	}
	return err
}

func UpdateSkuHotStatus(id, hotStatus int) error {

	sqlTpl := "update goods_sku set is_hot = ?, updated_time = ? WHERE id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(hotStatus, time.Now().Add(8*time.Hour), id)
	if err != nil {
		return fmt.Errorf("update sku hot status, id:%d, err:%v", id, err.Error())
	}
	return err
}

func UpdateSkuStatusByGoodsId(ctx context.Context, tx *sql.Tx, goodsId, status int) error {

	stmt, err := tx.Prepare("update goods_sku set `status` = ?, updated_time = ? where goods_id = ? and `status` <> ?")
	if err != nil {
		return err
	}
	_, err = stmt.ExecContext(ctx, status, time.Now().Add(8*time.Hour), goodsId, status)
	return err
}

func composeSkuSearchQuerySql(name string) string {

	// 没有条件查询
	if len(name) == 0 {
		return ""
	}
	sb := strings.Builder{}
	sb.WriteString(" WHERE k.`name` like '" + strings.TrimSpace(name) + "%' ")

	return sb.String()
}

/**
查询所有上架的sku 数量
*/

func CountGoodsSku(status int) int {

	stmt, err := common.DB.Prepare("select count(*) FROM goods_sku WHERE `status` = ?")
	if err != nil {
		common.Error.Println(err)
		return 0
	}
	defer stmt.Close()

	var count int
	if err = stmt.QueryRow(status).Scan(&count); err != nil {
		common.Error.Printf("err CountGoodsSku,%v", err)
		return 0
	}
	return count
}

// search helper 根据用户搜索的关键词，找出相关的sku

func ListSkuNameByKeyword(kw string) []string {

	stmt, err := common.DB.Prepare("select `name` FROM goods_sku WHERE `status` = 1 and `name` LIKE ?")
	if err != nil {
		common.Error.Println(err)
		return nil
	}
	defer stmt.Close()

	rows, err := stmt.Query(kw + "%")
	if err != nil {
		common.Error.Printf("%v", err)
		return nil
	}
	defer rows.Close()

	var list []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			common.Error.Println("err ListSkuNameByKeyword:", err)
			continue
		}
		list = append(list, name)
	}
	return list
}

func ListNewGoodsSku() ([]model.Sku, error) {

	stmt, err := common.DB.Prepare("SELECT id, `name`, main_img, real_price FROM goods_sku WHERE `status` = 1 AND week(created_time) = week(now())")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.Name, &k.MainImg, &k.RealPrice); err != nil {
			return nil, fmt.Errorf("app ListNewGoodsSku->商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func ListHotGoodsSku() ([]model.Sku, error) {

	stmt, err := common.DB.Prepare("SELECT id, `name`, main_img, real_price, description FROM goods_sku WHERE `status` = 1 AND is_hot = 1")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.Name, &k.MainImg, &k.RealPrice, &k.Description); err != nil {
			return nil, fmt.Errorf("app ListHotGoodsSku->商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func ListHotGoodsIdList() ([]int, error) {

	stmt, err := common.DB.Prepare("SELECT distinct goods_id FROM goods_sku WHERE `status` = 1 AND is_hot = 1")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []int
	for rows.Next() {
		var g int
		if err := rows.Scan(&g); err != nil {
			return nil, fmt.Errorf("app ListHotGoodsIdList->商品sku hot goods数据有误:%s", err.Error())
		}
		gs = append(gs, g)
	}
	return gs, nil
}

func GetSkuInfoById(id int) (*model.Sku, error) {

	sqlTpl := "SELECT k.id, k.`name`, k.description, k.price, k.real_price, k.main_img, k.goods_id FROM goods_sku k where k.id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var k model.Sku
	if err = stmt.QueryRow(id).Scan(&k.Id, &k.Name, &k.Description, &k.Price, &k.RealPrice,
		&k.MainImg, &k.GoodsId); err != nil {
		return nil, err
	}
	return &k, nil
}

func ListNeighbourSku(skuId int) ([]model.Sku, error) {

	stmt, err := common.DB.Prepare("select goods_id from goods_sku where id = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	var goodsId int
	if err = stmt.QueryRow(skuId).Scan(&goodsId); err != nil {
		common.Error.Printf("err ListNeighbourSku,%v", err)
		return nil, err
	}
	stmt, err = common.DB.Prepare("SELECT id, `name`, main_img, real_price, description FROM goods_sku WHERE goods_id = ? and `status` = 1 and id != ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(goodsId, skuId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.Name, &k.MainImg, &k.RealPrice, &k.Description); err != nil {
			return nil, fmt.Errorf("app ListNeighbourSku->商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func ListAllSku() ([]model.Sku, error) {

	sqlTpl := "select id, `name` FROM goods_sku"
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		var k model.Sku
		if err := rows.Scan(&k.Id, &k.Name); err != nil {
			return nil, fmt.Errorf("all sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func ListCollectGoods(memberId int) ([]model.Sku, error) {

	skuIds, err := ListSkuIdByMemberId(memberId)
	if err != nil {
		return nil, err
	}
	if skuIds == nil || len(skuIds) == 0 {
		return nil, nil
	}
	stmt, err := common.DB.Prepare("SELECT id, `name`, main_img, real_price, description FROM goods_sku WHERE id in (?" + strings.Repeat(",?", len(skuIds)-1) + ")")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	args := make([]interface{}, len(skuIds))
	for i, id := range skuIds {
		args[i] = id
	}
	rows, err := stmt.Query(args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.Name, &k.MainImg, &k.RealPrice, &k.Description); err != nil {
			return nil, fmt.Errorf("app 查询收藏商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}
